package puppy.db1;

import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;



import puppy.db1.DBHandler;
import puppy.db.DBParameters;

import puppy.webData.UrlData;
import puppy.webData.UserFormData;

/**
 * 
 * @author Sergio Duarte
 * 
 */
public class DBHandler implements DBParameters {

	/**
	 * @param args
	 */

	static private Connection con = null;

		
	/**
	 * Returns query text [0] and query description [1] for a query 
	 * specified with its id
	 *  
	 * 
	 * @param queryId
	 * @return
	 */
	public   static String[] getQueryInfo(Long queryId) {

		String[] q = new String[2];

		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st
					.executeQuery("select query,description from QuerySet where"
							+ " id=" + "" + queryId + "");

			while (rs.next()) {

				String query = rs.getString("query");

				String desc = rs.getString("description");

				q[0] = query;
				q[1] = desc;
			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return q;
	}
	
	
	
	public   static String getQueryReviews(Long queryId) {

		String q = new String();

		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st
					.executeQuery("select reviews from QuerySet where"
							+ " id=" + "" + queryId + "");

			while (rs.next()) {

				String query = rs.getString("reviews");

				

				q = query;
			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return q;
	}
	
	
	public static int getSurveyHits(int queryId) {

		String q = new String();
		int hits = 0;

		try {

			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/googleSurveys", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st.executeQuery("select hits from surveys where"
					+ " id=" + "" + queryId + "");

			while (rs.next()) {

				hits = rs.getInt("hits");

			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return hits;
	}
	
	
	

	public static String getSurveyRL(int queryId) {

		String s="";

		try {

			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/googleSurveys", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st.executeQuery("select url from surveys where"
					+ " id=" + "" + queryId + "");

			while (rs.next()) {

				s = rs.getString("url");

			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return s;
	}
	
	
	
	public static String[] getURLInfo(String url){
		
		
		
		
		String a []= new String[2];
		
		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");
			Statement st = con.createStatement();
			
			
			
			System.out.println("Query: " + "select title, snippet from QueryData where"
							+ " url=" + "\"" + url + "\"");
			ResultSet rs = st
					.executeQuery("select title, snippet from QueryData where"
							+ " url=" + "\"" + url + "\"");

			while (rs.next()) {

			
				String query = rs.getString("title");

				String desc = rs.getString("snippet");

				a[0] = query;
				a[1] = desc;
	
			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
		
		
		
		
	}
	
	
	
	
	public static LinkedList<String> getUrlsForQuery(Long queryId) {

		LinkedList<String> list = new LinkedList<String>();

		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st
					.executeQuery("select distinct url from QueryData where"
							+ " queryId=" + "'" + queryId + "'");

			while (rs.next()) {

				String url = rs.getString("url");

				list.add(url);

			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	public static int getKidsVersionQuery(int i) {

	

		String description[] = getQueryInfo((long) i);

		String text = description[1] + " for kids";

		int id_kids = 0;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st.executeQuery("select id from QuerySet where"
					+ " description=\"" +  text + "\"");

			while (rs.next()) {

				id_kids = rs.getInt("id");
			}

			con.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return id_kids;
	}
	
	
	
	
	public static LinkedList<UrlData> getUrlDataForQuery(Long queryId) {

		LinkedList<UrlData> list = new LinkedList<UrlData>();

		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st
					.executeQuery("select distinct url,snippet, title, id from QueryData where"
							+ " queryId=" + "'" + queryId + "' and rank<=10 and searchEngine=1");

			while (rs.next()) {

				String url = rs.getString("url");
				String snippet = rs.getString("snippet");
				String title = rs.getString("title");
				int id= rs.getInt("id");				
				
				UrlData u_data= new UrlData(url,title,snippet,id);
				
				list.add(u_data);

			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("Size url list:" + list.size());

		return list;
	}

	
	
	
	 synchronized public static LinkedList<Long> getQueriesID() {

		LinkedList<Long> set = new LinkedList<Long>();

		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			String connectionURL="jdbc:mysql://127.0.0.1:3306/puppyForm";
			Connection connection = DriverManager.getConnection(connectionURL,"root","thesis");
			
			Statement st = connection.createStatement();

			ResultSet rs = st.executeQuery("select id from QuerySet order by reviews" );
 
			while (rs.next()) {

				Long id = rs.getLong("id");
		//		log.error("Insertando " + id);
				set.add(id);

			}
			/*st.close();
			rs.close();*/
			connection.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		//	log.fatal("se totio 1", e);
			
		//	log.fatal("mensaje "+ e.getMessage());
		//	log.fatal(e.getErrorCode());
		
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
		
		//	log.fatal("se totio 2");
			e.printStackTrace();
		} 
		return set;
	}
	 
	 
	 synchronized public static LinkedList<Long> getQueriesID2() {

			LinkedList<Long> set = new LinkedList<Long>();

			try {
				
				Class.forName("com.mysql.jdbc.Driver");
				String connectionURL="jdbc:mysql://127.0.0.1:3306/puppyForm";
				Connection connection = DriverManager.getConnection(connectionURL,"root","thesis");
				
				Statement st = connection.createStatement();

				ResultSet rs = st.executeQuery("select id from QuerySet");

				while (rs.next()) {

					Long id = rs.getLong("id");
			//		log.error("Insertando " + id);
					set.add(id);

				}
				/*st.close();
				rs.close();*/
				connection.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();

			
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
			
				e.printStackTrace();
			} 
			return set;
		}
		 
		 
	synchronized public static LinkedList<Long> setQueryState(Long id,
			int status) {

		LinkedList<Long> set = new LinkedList<Long>();

		try {

			Class.forName("com.mysql.jdbc.Driver");
			String connectionURL = "jdbc:mysql://127.0.0.1:3306/puppyForm";
			Connection connection = DriverManager.getConnection(connectionURL,
					"root", "thesis");

			Statement st = connection.createStatement();

			st.executeUpdate("update QuerySet SET state=" + status
					+ " where id=" + id);

			st.close();
			connection.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			// log.fatal("se totio 1", e);

			// log.fatal("mensaje "+ e.getMessage());
			// log.fatal(e.getErrorCode());

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block

			// log.fatal("se totio 2");
			e.printStackTrace();
		}
		return set;
	}
	
	
	synchronized public static LinkedList<Long> setQueryReviews(Long id,
			String reviews) {

		LinkedList<Long> set = new LinkedList<Long>();

		try {

			Class.forName("com.mysql.jdbc.Driver");
			String connectionURL = "jdbc:mysql://127.0.0.1:3306/puppyForm";
			Connection connection = DriverManager.getConnection(connectionURL,
					"root", "thesis");

			Statement st = connection.createStatement();

			st.executeUpdate("update QuerySet SET reviews=" + reviews
					+ " where id=" + id);

			st.close();
			connection.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			// log.fatal("se totio 1", e);

			// log.fatal("mensaje "+ e.getMessage());
			// log.fatal(e.getErrorCode());

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block

			// log.fatal("se totio 2");
			e.printStackTrace();
		}
		return set;
	}
	 
	
	
	synchronized public static LinkedList<Long> increaseQueryReviews(Long id) {

		LinkedList<Long> set = new LinkedList<Long>();

		try {

			Class.forName("com.mysql.jdbc.Driver");
			String connectionURL = "jdbc:mysql://127.0.0.1:3306/puppyForm";
			Connection connection = DriverManager.getConnection(connectionURL,
					"root", "thesis");

			Statement st = connection.createStatement();

			String reviews = getQueryReviews(id);
System.out.println("qUERY REVIEWS ID:" + id);
			Integer re;
			if (reviews != null) {
			//	System.out.println("id: " + id + "\t" + reviews);
				re = new Integer(reviews)+ 1;
			} else {
				re = new Integer(0) + 1;

			}
			
			st.executeUpdate("update QuerySet SET reviews=" + re + " where id="+ id);

			st.close();
			connection.close();

		} catch (SQLException e) {

			e.printStackTrace();
		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		}
		return set;
	}
	
	
	
	synchronized public static LinkedList<Long> increaseSurveyHits(int id) {

		LinkedList<Long> set = new LinkedList<Long>();

		try {

			Class.forName("com.mysql.jdbc.Driver");
			String connectionURL = "jdbc:mysql://127.0.0.1:3306/googleSurveys";
			Connection connection = DriverManager.getConnection(connectionURL,
					"root", "thesis");

			Statement st = connection.createStatement();

			int reviews = getSurveyHits(id);

			
			reviews ++;
			System.out.println(id + "\t new re\t" + reviews);

			st.executeUpdate("update surveys SET hits=" + reviews + " where id="+ id);

			st.close();
			connection.close();

		} catch (SQLException e) {

			e.printStackTrace();
		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		}
		return set;
	}
	
	 /**
	  * Get set of web entry ids that share the same url 
	  * 
	  * @param url
	  * @return
	  */
	 
	 
	 
	synchronized public static void resetQueries(){
		 
		 
		 
		 LinkedList<Long> list = getQueriesID2();
		 
		 
		 for(int i=0; i<list.size(); i++){
			 
			 
			 setQueryState(list.get(i),0);
		 }
	 }
	 
	public static HashSet<Long> getEntriesWithSameURL(String url) {

		HashSet<Long> ids = new HashSet<Long>();

		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/puppyForm", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st.executeQuery("select id from QueryData where "
					+ " url='" + url + "'");

			while (rs.next()) {

				Long id = rs.getLong("id");

				ids.add(id);

			}
			st.close();
			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ids;
	}
	
	
	

	
	 
	public static void insertUserFormInstance(UserFormData data,
			String url) {

		
		HashSet<Long> ids = getEntriesWithSameURL(url);
		
		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");

			Iterator<Long> iter = ids.iterator();

			while (iter.hasNext()) {

				Long id = iter.next();
				
			//	System.out.println("Writting:" +id +"\t"+data.getSqlDate());

				String INSERT_RECORD = "INSERT INTO webAnnotation(webID,"
						+ " relevant, appropriate, time,"
						+ "userID)" + " values (?,?,?,?,?)";
				
				PreparedStatement pstmt = con.prepareStatement(INSERT_RECORD);
			//	System.out.println("Anadiendo data: " + id);
				pstmt.setLong(1, id); // webID
				pstmt.setShort(2, (short)data.getRelevancy()); // relevant
				pstmt.setShort(3, (short)data.getAppropriate()); // appropriate
				pstmt.setTimestamp(4, data.getSqlDate()); // time stamp
				pstmt.setString(5, data.getUserID()); // user id
				
				pstmt.execute();
			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	
	synchronized public static int getNextQuery(){

			int id =0;
			
			LinkedList<Integer> ids = new LinkedList<Integer>();
			try {
				
				Class.forName("com.mysql.jdbc.Driver");
				con = DriverManager.getConnection(
						"jdbc:mysql://localhost/puppyForm", "root", "thesis");
				Statement st = con.createStatement();

				ResultSet rs = st
						.executeQuery("" +
								"SELECT id, description FROM QuerySet WHERE rank< reviews = (SELECT MIN(reviews) FROM QuerySet)");

				while (rs.next()) {

					id = rs.getInt("id");
					 
					ids.add(id); 
					 
				}

				con.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			
			//select random number from the list
			
			if(ids!=null && ids.size()>0){
				
				int index =  (int) (Math.random() * ids.size());
				id = ids.get(index);
				
				
			}
			
			
			return id;
		 
		 
	 }
	
	
	synchronized public static int getNextQuery(int rankLimit) {

		int id = 0;
		HashSet<String> set = new HashSet<String>();
		LinkedList<Integer> ids = new LinkedList<Integer>();
		try {

			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st
					.executeQuery(""
							+ "SELECT id, description FROM QuerySet WHERE rank<"
							+ rankLimit
							+ " and type<=2 && reviews = (SELECT MIN(reviews) FROM QuerySet)");

			while (rs.next()) {

				id = rs.getInt("id");
				String description = rs.getString("description");

				if (!set.contains(description)) {

					set.add(description);
					ids.add(id);

				}

				// ids.add(id);

			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// select random number from the list

		if (ids != null && ids.size() > 0) {

			int index = (int) (Math.random() * ids.size());
			id = ids.get(index);

		}

		return id;

	}
	
	synchronized public static int getNextURL(int queryId){

		int id =0;
		
		LinkedList<Integer> ids = new LinkedList<Integer>();
		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st
					.executeQuery("" +
							"SELECT id FROM QuerySet WHERE reviews = (SELECT MIN(reviews) FROM QuerySet)");

			while (rs.next()) {

				id = rs.getInt("id");
				 
				ids.add(id); 
				 
			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		
		//select random number from the list
		
		if(ids!=null && ids.size()>0){
			
			int index =  (int) (Math.random() * ids.size());
			id = ids.get(index);
			
			
		}
		
		
		return id;
	 
	 
 }
	
	public   static int getNextSurvey() {

		int id =0;
		
		LinkedList<Integer> ids = new LinkedList<Integer>();
		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/googleSurveys", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st
					.executeQuery("" +
							"SELECT id FROM surveys WHERE hits = (SELECT MIN(hits) FROM surveys)");

			while (rs.next()) {

				id = rs.getInt("id");
				 
				ids.add(id); 
				 
			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		
		//select random number from the list
		
		if(ids!=null && ids.size()>0){
			
			int index =  (int) (Math.random() * ids.size());
			id = ids.get(index);
			
			
		}
		
		
		return id;
	}
	
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
/*		DBHandler.getUrlForQuery("1");
		UserFormData data = new UserFormData();
		data.setAge12(true);
		data.setAppropriate(true);
		data.setRelevant(false);
		data.setDuration((long) 200);
		data.setQueryID((long) 10);

		data.setGender((short) 1);
		java.sql.Timestamp sqlDate = new java.sql.Timestamp(
				new java.util.Date().getTime());
		data.setSqlDate(sqlDate);
		data.setUserID((long) 200);

		HashSet<Long> set = new HashSet<Long>();
		set.add(new Long(100));
		DBHandler.insertUserFormInstance(data, set);*/
		LinkedList<Long> list = new LinkedList<Long>();
		//list = DBHandler.getQueriesID();
		 float x = (float)(Math.random());
		//System.out.println(DBHandler.getQueryInfo(new Long(1)));

		 //DBHandler.increaseSurveyHits(14);
		
		 System.out.println(DBHandler.getNextQuery());
		
		//System.out.println(currentTimeInSeconds);

	}

}
